larvitdb

larvitdb

DB pool wrapper for node.js This module is used to share a mysql/mariadb pool of connections between modules, classes, files etc.

It also logs with winston if there is a database error, so you do not need to fetch the database error manually each time.

Installation

npm i larvitdb

Usage

The module must first be required and then configured. Make this in your main application file:

const winston = require('winston'),
      log     = winston.createLogger({'transports': [new winston.transports.Console()]}),
      db      = require('larvitdb');

db.setup({
	'host':              '127.0.0.1',                   // Do not use with socketPath
	'socketPath':        '/var/run/mysqld/mysqld.sock', // Do not use with host
	'connectionLimit':   10,                            // Connections in the pool
	'user':              'foo',
	'password':          'bar',
	'charset':           'utf8_general_ci',
	'supportBigNumbers': true,
	'database':          'my_database_name',
	'log':               log                            // Will default to a simple console logger if not provided
});

See list of native options here. Then you can just require the module in your other files for usage, like this:

A direct query

const db = require('larvitdb');

db.query('SELECT 1 + 1 AS solution', function(err, rows, fields) {
	console.log('dbmodel: The solution is: ', rows[0].solution);
});

Or, if a connection is needed:

const db = require('larvitdb');

db.pool.getConnection(function(err, dbCon) {
	const sql = 'SELECT * FROM users WHERE username LIKE ' + dbCon.escape(postData);

	dbCon.query(sql, function(err, rows) {
		dbCon.release(); // Always release your connection when the query is done

		if (err)
			throw err;
	});
});

You dont need to get a connection to escape though. You can do like this:

const db = require('larvitdb');

db.query('SELECT * FROM users WHERE id = ?', [userId], function(err, results) {
  // ...
});

Advanced configuration - recoverable errors

Sometimes recoverable errors happend in the database. One such example is deadlocks in a cluster. Here we'll provide an example of how to make the database layer retry a query 5 times if a deadlock happends, before giving up.

const db = require('larvitdb');

db.setup({
	'connectionLimit':   10,
	'socketPath':        '/var/run/mysqld/mysqld.sock',
	'user':              'foo',
	'password':          'bar',
	'charset':           'utf8_general_ci',
	'supportBigNumbers': true,
	'database':          'my_database_name',
	'retries':           5, // Defaults to 3 if omitted
	'recoverableErrors': ['PROTOCOL_CONNECTION_LOST', 'ER_LOCK_DEADLOCK'] // What error codes to retry, these are the defaults
});

// If this query fails with a deadlock, it will be retried up to 5 times.
// On each retry a warning will be logged with winston
// If the 5th retry fails, an error will be logged and the callback will be called with an error
db.query('DELETE FROM tmpTable LIMIT 10');

Advanced configuration - long running queries

By default a warning is logged if a query runs longer than 10k ms (10 seconds). This number can be tweaked like this for 20 seconds:

db.setup({
	...
	'longQueryTime': 20000
});

or like this to disable the warnings:

db.setup({
	...
	'longQueryTime': false
});

## Custom functions

### Remove all tables from current database

This function will clean the current database from all tables.

```javascript
const db = require('larvitdb');

db.removeAllTables();

Important about time zones!

All sessions with the database will be set to UTC time!

When setting datetime stuff, use the javascript native Date object, like this:

db.query('INSERT INTO users (created, username) VALUES(?,?)', [new Date(), 'foobar']);

If you do, this library will convert the time zone info for you.

However, please note that all date time you get back from the database will be in UTC.

Version history

2.0.0

• Always set all new sessions to UTC time zone
• Convert Date objects to UTC datetimestamps that fits MariaDB and MySQL

Major from 1.x to 2.0 since this might break functionality for some implementations.